NoticeExecReport

V8 Message Definiton

METADATA

Attribute	Value
Topic	2450-liquidity-notice
MLink Token	ClientTrading
Product	SRTrade
accessType	SELECT

Table Definition

Field	Type	Key	Default Value	Comment
noticeNumber	CHAR(19)	PRI	'0000-0000-0000-0000'	AuctionNoticenoticeNumber
baseParentNumber	CHAR(19)	PRI	'0000-0000-0000-0000'
ticker_at	enum - AssetType		'None'	underlier ticker
ticker_ts	enum - TickerSrc		'None'	underlier ticker
ticker_tk	VARCHAR(12)		''	underlier ticker
tradeDate	DATE		'1900-01-01'
accnt	VARCHAR(16)		''	SR Accnt
clientFirm	VARCHAR(16)		''	SR ClientFirm
noticeDttm	DATETIME(6)		'1900-01-01 00:00:00.000000'	either notice create datetime or notice arrivalSR datetime high precision
responseId	VARCHAR(24)		''	from NoticeResponseresponseId most recently processed response to noticeNumber for this accntclientFirm pair
spdrSource	enum - SpdrSource		'None'	AutoResponder MLINK FIX ICEChat TradeAp responders
stageType	enum - SpdrStageType		'None'	ats order staged or not
auctionType	enum - AuctionType		'None'	Exch Exposure Exch Price Improve Exch Facilitation Exch Solicitation SR ATS Block SR ATS Flash
auctionSource	enum - AuctionSource		'None'	Source of the auction notice eg SRC CBOE MIAX etc
createDttm	DATETIME(6)		'1900-01-01 00:00:00.000000'	ats order arrival dttm high precision
side	enum - BuySell		'None'
size	INT		0
activeSize	INT		0	active order size zero for ActiveHold
reachRule	enum - ReachRule		'None'	size condition
refPrice	DOUBLE		0	effPrice price de uMid refUPrc 05 ga uMid refUPrc2
refUPrc	DOUBLE		0	reference price for tied to underlier orders
uRefType	enum - URefType		'None'	type of reference price uBid uAsk uMid
refDe	FLOAT		0	reference delta for tied to underlier orders
refGa	FLOAT		0	reference gamma for tied to underlier orders
roundRule	enum - RoundRule		'None'	Exact 100 or Fuzzy 90 default is Exact
stepUpIncr	DOUBLE		0	additional price increment best price only used if auction is competitive
riskGroupId	CHAR(19)		'0000-0000-0000-0000'	associated riskGroupID and SpdrRiskGroupControl if any
strategy	VARCHAR(32)		''	user strategy field visible on SR tools
userData1	TINYTEXT		''	user data field free text from NoticeResponse
orderStatus	enum - SpdrOrderStatus		'PendNew'	ats order status
orderDetail	TINYTEXT		''	additional detail
lastUPrc	DOUBLE		0	effective uPrc midmarket from last trial pass
lastTrialPrc	DOUBLE		0	effective response price from last trial pass
leavesQty	INT		0	open order quantity
cumFillQty	INT		0	pkg cumFillQty if MLeg
avgFillPrice	DOUBLE		0	pkg avgFillPrice if MLeg
completionType	enum - CompletionType		'None'	None DAC POC TAS TACO BTIC
completionState	enum - CompletionState		'None'
avgCompletionPrice	DOUBLE		0	completion trade price final trade price also avg completion price
addLatency	DOUBLE		0	latency from notice arrivalSR to external response send in seconds OR latency from notice create to response accepted in seconds SRC Auctions
ackLatency	DOUBLE		0	latency from external response send to external response ack FRT in seconds external auctions only
numOptLegs	INT		0	number of option legs
spreadClass	enum - SpreadClass		'None'	spread class eg Call Put RevCon Box JRoll etc
spreadFlavor	enum - SpreadFlavor		'None'	spread flavor Normal Flipped of this order
containsHedge	enum - YesNo		'None'	if this order contains a stockfuture leg
containsFlex	enum - YesNo		'None'	if this order contains flex legs
containsMultiHedge	enum - YesNo		'None'	contains MultiHedge corp action adjusted options
responseResult	enum - NoticeResult		'None'
responseTime	SMALLINT		0	notice response arrival time milliseconds since auction start
prtPrice	DOUBLE		0	reported print price pkgPrice if MLeg
prtSize	INT		0	reported print size pgkSize if MLeg
prtTime	DATETIME(6)		'1900-01-01 00:00:00.000000'	reported print time 1st print if MLeg
prtUBid	DOUBLE		0	best estimate of uBid auction print time
prtUAsk	DOUBLE		0	best estimate of uAsk auction print time
timestamp	DATETIME(6)		'1900-01-01 00:00:00.000000'
OrderLegsList	JSON		'JSON_ARRAY()'

PRIMARY KEY DEFINITION (Unique)

Field	Sequence
noticeNumber	1
baseParentNumber	2

JSON Block (OrderLegsList)

Field	Type	Comment
secKey	enum - secKey
secType	enum - SpdrKeyType
side	enum - BuySell
ratio	enum - ratio
positionType	enum - LegPositionType
legCumFillQty	enum - legCumFillQty
legAvgFillPrice	enum - legAvgFillPrice
legCumCmplQty	enum - legCumCmplQty
legAvgCmplPrice	enum - legAvgCmplPrice
legCmplSecKey	enum - legCmplSecKey	final settlement secKey contract that DAC TAS TACO etc will convert to might not be valid until completed
legCmplSecType	enum - SpdrKeyType	usually Future or Option None if not yet valid

CREATE TABLE EXAMPLE QUERY

CREATE TABLE `SRTrade`.`MsgNoticeExecReport` (
    `noticeNumber` CHAR(19) NOT NULL DEFAULT '0000-0000-0000-0000' COMMENT 'AuctionNotice.noticeNumber',
    `baseParentNumber` CHAR(19) NOT NULL DEFAULT '0000-0000-0000-0000',
    `ticker_at` ENUM('None','EQT','IDX','BND','CUR','COM','FUT','SYN','WAR','FLX','MUT','SPD','MM','MF','COIN','TOKEN','ANY') NOT NULL DEFAULT 'None' COMMENT 'underlier ticker',
    `ticker_ts` ENUM('None','SR','NMS','CME','ICE','CFE','CBOT','NYMEX','COMEX','RUT','CIDX','ARCA','NYSE','OTC','NSDQ','MFQS','MIAX','DJI','CUSIP','ISIN','BXE','SCE','ANY','CXE','DXE','NXAM','NXBR','NXLS','NXML','NXOS','NXP','EUREX','CEDX','ICEFE') NOT NULL DEFAULT 'None' COMMENT 'underlier ticker',
    `ticker_tk` VARCHAR(12) NOT NULL DEFAULT '' COMMENT 'underlier ticker',
    `tradeDate` DATE NOT NULL DEFAULT '1900-01-01' COMMENT '',
    `accnt` VARCHAR(16) NOT NULL DEFAULT '' COMMENT 'SR Accnt',
    `clientFirm` VARCHAR(16) NOT NULL DEFAULT '' COMMENT 'SR ClientFirm',
    `noticeDttm` DATETIME(6) NOT NULL DEFAULT '1900-01-01 00:00:00.000000' COMMENT 'either notice create datetime or notice arrival@SR datetime (high precision)',
    `responseId` VARCHAR(24) NOT NULL DEFAULT '' COMMENT 'from NoticeResponse.responseId (most recently processed response to noticeNumber for this accnt/clientFirm pair)',
    `spdrSource` ENUM('None','SpdrTicket','SpdrSingle','SRSE','FIX','HedgeTool','TradeHedge','OpenHedge','AutoHedge','Orphan','RiskManager','OrderManager','ManagedOrder','RFQRespSrvr','Legger','SRSEDrop','FixDrop','TicketDrop','SysTest','RFRResponse','AllocOmni','AllocClient','CertGateway','MLegResponse','LeggerX','DropManager','AutoHedgeSrvr','AuctionStrategySrvr','AllocBlockFace','AllocBlockCust','IceChatGateway','EXS2SRC','MLinkResponse','AutoResponderVD','AutoResponderRC','AutoResponderSN','AutoResponderBX','MLink','SymChatGateway','RiskDrop','PolicyOpn','PolicyTrd','PolicyExe') NOT NULL DEFAULT 'None' COMMENT 'AutoResponder, MLINK, FIX, ICEChat, TradeAp responders',
    `stageType` ENUM('None','ModifyAny','ModifyAlgo') NOT NULL DEFAULT 'None' COMMENT 'ats order staged or not',
    `auctionType` ENUM('None','Exposure','Improvement','Facilitation','Solicitation','Opening','Closing','RFQ','Block','Flash','BlockDAC','BlockPOC','BlockPCV') NOT NULL DEFAULT 'None' COMMENT 'Exch Exposure, Exch Price Improve, Exch Facilitation, Exch Solicitation, SR ATS Block, SR ATS Flash',
    `auctionSource` ENUM('None','SRC','AMEX','BOX','CBOE','ISE','NYSE','PHLX','NSDQ','BATS','C2','NQBX','MIAX','GMNI','EDGO','MCRY','MPRL','EMLD','MEMX','CME','CBOT','NYMEX','COMEX','ICE','EUREX') NOT NULL DEFAULT 'None' COMMENT 'Source of the auction notice (eg. SRC, CBOE, MIAX, etc.)',
    `createDttm` DATETIME(6) NOT NULL DEFAULT '1900-01-01 00:00:00.000000' COMMENT 'ats order arrival dttm (high precision)',
    `side` ENUM('None','Buy','Sell') NOT NULL DEFAULT 'None',
    `size` INT NOT NULL DEFAULT 0,
    `activeSize` INT NOT NULL DEFAULT 0 COMMENT 'active order size (zero for ActiveHold)',
    `reachRule` ENUM('None','Delayed','Passive','WeakOnly','RespondOnly','FullSize','ISOSweep','AllOrNone','QtyOrMore','UpToQty','AtMost25','AtMost50','MinTakeFee') NOT NULL DEFAULT 'None' COMMENT 'size condition',
    `refPrice` DOUBLE NOT NULL DEFAULT 0 COMMENT 'effPrice = price + de * (uMid - refUPrc) + 0.5 * ga * (uMid - refUPrc)^2',
    `refUPrc` DOUBLE NOT NULL DEFAULT 0 COMMENT 'reference price for tied to underlier orders',
    `uRefType` ENUM('None','UBid','UAsk','UMid') NOT NULL DEFAULT 'None' COMMENT 'type of reference price (uBid, uAsk, uMid)',
    `refDe` FLOAT NOT NULL DEFAULT 0 COMMENT 'reference delta for tied to underlier orders',
    `refGa` FLOAT NOT NULL DEFAULT 0 COMMENT 'reference gamma for tied to underlier orders',
    `roundRule` ENUM('None','Exact','Fuzzy') NOT NULL DEFAULT 'None' COMMENT 'Exact (100%) or Fuzzy (90%) [default is Exact]',
    `stepUpIncr` DOUBLE NOT NULL DEFAULT 0 COMMENT 'additional price increment (best price) (only used if auction is competitive)',
    `riskGroupId` CHAR(19) NOT NULL DEFAULT '0000-0000-0000-0000' COMMENT 'associated riskGroupID (and SpdrRiskGroupControl) (if any)',
    `strategy` VARCHAR(32) NOT NULL DEFAULT '' COMMENT 'user strategy field (visible on SR tools)',
    `userData1` TINYTEXT NOT NULL DEFAULT '' COMMENT 'user data field (free text) (from NoticeResponse)',
    `orderStatus` ENUM('PendNew','New','PendClose','Closed','Rejected','SendReject') NOT NULL DEFAULT 'PendNew' COMMENT 'ats order status',
    `orderDetail` TINYTEXT NOT NULL DEFAULT '' COMMENT 'additional detail',
    `lastUPrc` DOUBLE NOT NULL DEFAULT 0 COMMENT 'effective uPrc (mid-market) from last trial pass',
    `lastTrialPrc` DOUBLE NOT NULL DEFAULT 0 COMMENT 'effective response price from last trial pass',
    `leavesQty` INT NOT NULL DEFAULT 0 COMMENT 'open order quantity',
    `cumFillQty` INT NOT NULL DEFAULT 0 COMMENT 'pkg cumFillQty if MLeg',
    `avgFillPrice` DOUBLE NOT NULL DEFAULT 0 COMMENT 'pkg avgFillPrice if MLeg',
    `completionType` ENUM('None','DAC','POC','TAS','TACO','BTIC') NOT NULL DEFAULT 'None' COMMENT 'None, DAC, POC, TAS, TACO, BTIC',
    `completionState` ENUM('None','Prelim','Complete') NOT NULL DEFAULT 'None',
    `avgCompletionPrice` DOUBLE NOT NULL DEFAULT 0 COMMENT 'completion trade price (final trade price) [also avg completion price]',
    `addLatency` DOUBLE NOT NULL DEFAULT 0 COMMENT 'latency from notice arrival@SR to external response send (in seconds) OR latency from notice create to response accepted (in seconds) (SRC Auctions)',
    `ackLatency` DOUBLE NOT NULL DEFAULT 0 COMMENT 'latency from external response send to external response ack (FRT) (in seconds) (external auctions only)',
    `numOptLegs` INT NOT NULL DEFAULT 0 COMMENT 'number of option legs',
    `spreadClass` ENUM('None','Stk','Fut','Call','Put','Synth','RevCon','Box','JRoll','Roll','Straddle','Strangle','CSpread','PSpread','VStrip','VSpread','HStrip','HSpread','BFly','RiskRev','Mixed','VarSwap','Pair','ExpPair','VolPair') NOT NULL DEFAULT 'None' COMMENT 'spread class (eg. Call, Put, RevCon, Box, JRoll, etc.)',
    `spreadFlavor` ENUM('None','Normal','Flipped') NOT NULL DEFAULT 'None' COMMENT 'spread flavor (Normal, Flipped) of this order',
    `containsHedge` ENUM('None','Yes','No') NOT NULL DEFAULT 'None' COMMENT 'if this order contains a stock/future leg',
    `containsFlex` ENUM('None','Yes','No') NOT NULL DEFAULT 'None' COMMENT 'if this order contains flex leg(s)',
    `containsMultiHedge` ENUM('None','Yes','No') NOT NULL DEFAULT 'None' COMMENT 'contains MultiHedge (corp action adjusted) options',
    `responseResult` ENUM('None','FullSize','AllocSize','PriceMiss','TooLate','OtherMiss','DidNotTrade','Pending') NOT NULL DEFAULT 'None',
    `responseTime` SMALLINT NOT NULL DEFAULT 0 COMMENT 'notice response arrival time (milliseconds since auction start)',
    `prtPrice` DOUBLE NOT NULL DEFAULT 0 COMMENT 'reported print price (pkgPrice if MLeg)',
    `prtSize` INT NOT NULL DEFAULT 0 COMMENT 'reported print size (pgkSize if MLeg)',
    `prtTime` DATETIME(6) NOT NULL DEFAULT '1900-01-01 00:00:00.000000' COMMENT 'reported print time (1st print if MLeg)',
    `prtUBid` DOUBLE NOT NULL DEFAULT 0 COMMENT 'best estimate of uBid @ auction print time',
    `prtUAsk` DOUBLE NOT NULL DEFAULT 0 COMMENT 'best estimate of uAsk @ auction print time',
    `timestamp` DATETIME(6) NOT NULL DEFAULT '1900-01-01 00:00:00.000000',
    `OrderLegsList` JSON NOT NULL DEFAULT JSON_ARRAY() CHECK(JSON_VALID(OrderLegsList)),
    CONSTRAINT nonnegative_noticeNumber CHECK(ASCII(noticeNumber) < 56),
    CONSTRAINT nonnegative_baseParentNumber CHECK(ASCII(baseParentNumber) < 56),
    CONSTRAINT nonnegative_riskGroupId CHECK(ASCII(riskGroupId) < 56),
    PRIMARY KEY USING HASH (`noticeNumber`,`baseParentNumber`)
) ENGINE=SRSE DEFAULT CHARSET=LATIN1 COMMENT='';

SELECT TABLE EXAMPLE QUERY

SELECT
    `noticeNumber`,
    `baseParentNumber`,
    `ticker_at`,
    `ticker_ts`,
    `ticker_tk`,
    `tradeDate`,
    `accnt`,
    `clientFirm`,
    `noticeDttm`,
    `responseId`,
    `spdrSource`,
    `stageType`,
    `auctionType`,
    `auctionSource`,
    `createDttm`,
    `side`,
    `size`,
    `activeSize`,
    `reachRule`,
    `refPrice`,
    `refUPrc`,
    `uRefType`,
    `refDe`,
    `refGa`,
    `roundRule`,
    `stepUpIncr`,
    `riskGroupId`,
    `strategy`,
    `userData1`,
    `orderStatus`,
    `orderDetail`,
    `lastUPrc`,
    `lastTrialPrc`,
    `leavesQty`,
    `cumFillQty`,
    `avgFillPrice`,
    `completionType`,
    `completionState`,
    `avgCompletionPrice`,
    `addLatency`,
    `ackLatency`,
    `numOptLegs`,
    `spreadClass`,
    `spreadFlavor`,
    `containsHedge`,
    `containsFlex`,
    `containsMultiHedge`,
    `responseResult`,
    `responseTime`,
    `prtPrice`,
    `prtSize`,
    `prtTime`,
    `prtUBid`,
    `prtUAsk`,
    `timestamp`,
    `OrderLegsList`
FROM `SRTrade`.`MsgNoticeExecReport`
WHERE 
    /* Replace with a CHAR(19) */
    `noticeNumber` = 'Example_noticeNumber'
  AND
    /* Replace with a CHAR(19) */
    `baseParentNumber` = 'Example_baseParentNumber';

Doc Columns Query

SELECT * FROM SRTrade.doccolumns WHERE TABLE_NAME='NoticeExecReport' ORDER BY ordinal_position ASC;